package com.kaibes.orm.spring.jdbc;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;

import com.kaibes.orm.core.core.ColumnField;
import com.kaibes.orm.core.core.ObjectStringConverter;
import com.kaibes.orm.core.core.SqlRunner;
import com.kaibes.orm.core.core.TableColumn;
import com.kaibes.orm.core.sql.DeleteSql;
import com.kaibes.orm.core.sql.InsertSql;
import com.kaibes.orm.core.sql.SelectSql;
import com.kaibes.orm.core.sql.UpdateSql;
import com.kaibes.orm.core.util.TableUtils;

@Component
public class SqlRunnerImpl implements SqlRunner {

    @Value("${kaibes.orm.show-sql:false}")
    private boolean showSql;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectStringConverter objectStringConverter;

    private void print(String str) {
        if (showSql) {
            System.out.println(str);
        }
    }

    private boolean isBasicClass(Class<?> clazz) {
        return clazz == Integer.TYPE || clazz == Integer.class || clazz == Long.TYPE || clazz == Long.class
                || clazz == String.class || clazz == BigDecimal.class || clazz == Boolean.TYPE || clazz == Boolean.class
                || clazz == Character.TYPE || clazz == Character.class || clazz == Byte.TYPE || clazz == Byte.class
                || clazz == Short.TYPE || clazz == Short.class || clazz == Float.TYPE || clazz == Float.class
                || clazz == Double.TYPE || clazz == Double.class || clazz == LocalDate.class
                || clazz == LocalDateTime.class || clazz == BigInteger.class;
    }

    private int setObject(int i, PreparedStatement ps, List<ColumnField> columnMaps, Object data)
            throws SQLException, IllegalArgumentException, IllegalAccessException {
        for (ColumnField columnMap : columnMaps) {
            Class<?> type = columnMap.getField().getType();
            Object obj = columnMap.getField().get(data);
            if (obj == null) {
                ps.setObject(++i, null);
            } else if (type.isEnum()) {
                Enum<?> value = (Enum<?>) obj;
                obj = value.name();
                ps.setObject(++i, obj);
            } else if (isBasicClass(type)) {
                ps.setObject(++i, obj);
            } else {
                ps.setObject(++i, objectStringConverter.object2String(obj));
            }
        }
        return i;
    }

    private void setObject(PreparedStatement ps, List<Object> objectList) throws SQLException {
        int i = 0;
        for (Object obj : objectList) {
            if (obj == null) {
                ps.setObject(++i, obj);
            } else {
                Class<? extends Object> type = obj.getClass();
                if (type.isEnum()) {
                    Enum<?> value = (Enum<?>) obj;
                    obj = value.name();
                    ps.setObject(++i, obj);
                } else {
                    if (isBasicClass(type)) {
                        ps.setObject(++i, obj);
                    } else {
                        ps.setObject(++i, objectStringConverter.object2String(obj));
                    }
                }
            }
        }
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    private <E> E createObject(Class<E> clazz, int columnCount, List<Field> resultFields, ResultSet resultSet)
            throws InstantiationException, IllegalAccessException, SQLException, IllegalArgumentException,
            InvocationTargetException, NoSuchMethodException, SecurityException {
    	if (resultFields.size() != resultSet.getFetchSize()) {
    		
    	}
        E obj = clazz.getDeclaredConstructor().newInstance();
        for (int j = 0; j < columnCount; j++) {
            Field field = resultFields.get(j);
            
            Class type = field.getType();
            if (type.isEnum()) {
                String name = resultSet.getString(j + 1);
                Enum<?> value = Enum.valueOf(type, name);
                field.set(obj, value);
            } else {
                Object value = resultSet.getObject(j + 1);
                if (value == null) {
                    if (type == Integer.TYPE || type == Long.TYPE) {
                        field.set(obj, 0);
                    } else {
                        field.set(obj, null);
                    }
                } else {
                    if (isBasicClass(type)) {
                        value = resultSet.getObject(j + 1, type);
                    } else {
                        String temp = resultSet.getString(j + 1);
                        value = objectStringConverter.string2Object(temp, type);
                    }
                    field.set(obj, value);
                }
            }
        }
        return obj;
    }

    // ====================
    @SuppressWarnings("unchecked")
    @Override
    public <E> List<E> insert(InsertSql insertSql) {
        List<Object> datas = insertSql.getDataList();
        ColumnField idField = insertSql.getIdField();
        TableColumn tableColumn = insertSql.getTableColumn();

        StringBuilder sb = new StringBuilder();
        sb.append(insertSql.getSql().getHeader());
        for (int i = 0; i < datas.size(); i++) {
            sb.append(insertSql.getSql().getBody()).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        final String sql = sb.toString();
        print(sql+" "+insertSql.getDataList());
        
        List<ColumnField> columnMaps = tableColumn.getColumnFields();
        List<E> ids = new ArrayList<>();
        if (idField != null) {
            GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(con -> {
                PreparedStatement ps = con.prepareStatement(sql, new String[] { idField.getName() });
                int i = 0;
                for (Object data : datas) {
                    try {
                        i = setObject(i, ps, columnMaps, data);
                    } catch (IllegalArgumentException | IllegalAccessException e) {
                        throw new SQLException(e);
                    }
                }
                return ps;
            }, keyHolder);

            List<Map<String, Object>> keyList = keyHolder.getKeyList();
            for (int i = 0; i < datas.size(); i++) {
                Object temp = datas.get(i);
                Object key = keyList.get(i).get("GENERATED_KEY");
                if (key instanceof BigInteger) {
                    if (idField.getField().getType() == Short.class) {
                        key = ((BigInteger) key).shortValue();
                    } else if (idField.getField().getType() == Integer.class) {
                        key = ((BigInteger) key).intValue();
                    } else if (idField.getField().getType() == Long.class) {
                        key = ((BigInteger) key).longValue();
                    }
                }
                if (tableColumn.getIdColumn() != null) {
                    try {
                        tableColumn.getIdColumn().getField().set(temp, key);
                    } catch (IllegalArgumentException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                ids.add((E) key);
            }
        } else {
            jdbcTemplate.update(new PreparedStatementCreator() {

                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                    PreparedStatement preparedStatement = con.prepareStatement(sql);
                    int i = 0;
                    for (Object data : datas) {
                        try {
                            i = setObject(i, preparedStatement, columnMaps, data);
                        } catch (IllegalArgumentException | IllegalAccessException e) {
                            throw new SQLException(e);
                        }
                    }
                    return preparedStatement;
                }
            });
        }

        return ids;
    }

    @Override
    public <E> List<E> select(SelectSql selectSql) {
        String sql = selectSql.getSql();
        List<Object> objectList = selectSql.getArgList();
        TableColumn tableColumn = selectSql.getTableColumn();
        print(selectSql.getSql()+" "+selectSql.getArgList());
        List<E> resultList = jdbcTemplate.query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql);
                if (objectList != null) {
                    setObject(ps, objectList);
                }
                return ps;
            }
        }, new ResultSetExtractor<List<E>>() {

            @SuppressWarnings("unchecked")
            @Override
            public List<E> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                List<E> dataList = new ArrayList<>();
                if (tableColumn != null) {
                    Class<E> clazz = (Class<E>) tableColumn.getEntityClass();
                    List<ColumnField> columnFields = new ArrayList<>(tableColumn.getColumnFields());
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    List<Field> resultFields = new ArrayList<>();
                    for (int j = 1; j < columnCount + 1; j++) {
                        String columnName = metaData.getColumnName(j);
                        for (int i = 0; i < columnFields.size(); i++) {
                        	ColumnField element = columnFields.get(i);
                            if (element.getName().equals(columnName)) {
                                resultFields.add(element.getField());
                                columnFields.remove(i);
                                break;
                            }
                        }
                    }
                    if (resultFields.size() != columnCount) {
                    	throw new RuntimeException("数据模型("+clazz+")与数据表("+TableUtils.getTableName(clazz)+")的结构不一致！");
                    }
                    while (resultSet.next()) {
                        try {
                            dataList.add(createObject(clazz, columnCount, resultFields, resultSet));
                        } catch (InstantiationException | IllegalAccessException | IllegalArgumentException
                                | InvocationTargetException | NoSuchMethodException | SecurityException e) {
                            throw new SQLException(e);
                        }
                    }
                } else {
                    if (resultSet.next()) {
                        dataList.add((E)resultSet.getObject(1));
                    }
                }
                resultSet.close();
                return dataList;
            }
        });
        return resultList;
    }

    @Override
    public int delete(DeleteSql deleteSql) {
        String sql = deleteSql.getSql();
        List<Object> objectList = deleteSql.getArgList();
        print(sql+" "+deleteSql.getArgList());
        return jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql);
            if (objectList != null) {
                setObject(ps, objectList);
            }
            return ps;
        });
    }

    @Override
    public int update(UpdateSql updateSql) {
        String sql = updateSql.getSql();
        List<Object> objectList = updateSql.getArgList();
        print(sql+" "+updateSql.getArgList());
        return jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql);
            if (objectList != null) {
                setObject(ps, objectList);
            }
            return ps;
        });
    }

}
